<?php
namespace Index\Controller;

use Think\Controller;
use Think\Log;
class IndexController extends Controller
{

    function _initialize()
    {
        session("username", "admin_jspider");
    }


//    public function index()
//    {
//        $this->display(":demo");
//    }

    public function _empty($viewname)
    {
        $user = array();
        $user['username'] = session("username");
        $this->assign($user);
        $this->display(":$viewname");
    }


    public function test($startTime=0, $endTime=0){
        header("charset=utf-8");
        echo date("Ymd",strtotime("now")), "\n";
        echo date("Ymd",strtotime("-1 week Monday")), "\n";
        echo date("Ymd",strtotime("-1 week Sunday")), "\n";
        echo date("Ymd",strtotime("+0 week Monday")), "\n";
        echo date("Ymd",strtotime("+0 week Sunday")), "\n";

        echo "*********第几个月:";
        echo date('n');
        echo "*********本周周几:";
        echo date("w");
        echo "*********本月天数:";
        echo date("t");
        echo "*********";

        echo '<br>上周起始时间:<br>';
        echo date("Y-m-d H:i:s",mktime(0, 0 , 0,date("m"),date("d")-date("w")+1-7,date("Y"))),"\n";
        echo date("Y-m-d H:i:s",mktime(23,59,59,date("m"),date("d")-date("w")+7-7,date("Y"))),"\n";
        echo '<br>本周起始时间:<br>';
        echo date("Y-m-d H:i:s",mktime(0, 0 , 0,date("m"),date("d")-date("w")+1,date("Y"))),"\n";
        echo date("Y-m-d H:i:s",mktime(23,59,59,date("m"),date("d")-date("w")+7,date("Y"))),"\n";

        //从第几周找出该周的开始日期和结束日期
        $dayNumber = date('W') * 7;
        $weekDayNumber = date("w", mktime(0, 0, 0, 1, $dayNumber, date("Y")));//当前周的第几天
        $startNumber = $dayNumber - $weekDayNumber;
        echo date("Y-m-d", mktime(0, 0, 0, 1, $startNumber + 1, date("Y")));//开始日期
        echo date("Y-m-d", mktime(0, 0, 0, 1, $startNumber + 7, date("Y")));//结束日期

        echo '<br>上月起始时间:<br>';
        echo date("Y-m-d H:i:s",mktime(0, 0 , 0,date("m")-1,1,date("Y"))),"\n";
        echo date("Y-m-d H:i:s",mktime(23,59,59,date("m") ,0,date("Y"))),"\n";
        echo '<br>本月起始时间:<br>';
        echo date("Y-m-d H:i:s",mktime(0, 0 , 0,date("m"),1,date("Y"))),"\n";
        echo date("Y-m-d H:i:s",mktime(23,59,59,date("m"),date("t"),date("Y"))),"\n";

        $season = ceil((date('n'))/3);//当月是第几季度
        echo '<br>本季度起始时间:<br>';
        echo date('Y-m-d H:i:s', mktime(0, 0, 0,$season*3-3+1,1,date('Y'))),"\n";
        echo date('Y-m-d H:i:s', mktime(23,59,59,$season*3,date('t',mktime(0, 0 , 0,$season*3,1,date("Y"))),date('Y'))),"\n";

        $season = ceil((date('n'))/3)-1;//上季度是第几季度
        echo '<br>上季度起始时间:<br>';
        echo date('Y-m-d H:i:s', mktime(0, 0, 0,$season*3-3+1,1,date('Y'))),"\n";
        echo date('Y-m-d H:i:s', mktime(23,59,59,$season*3,date('t',mktime(0, 0 , 0,$season*3,1,date("Y"))),date('Y'))),"\n";
    }

    public function test2(){
        $season = ceil((date('n'))/3);//当月是第几季度
        echo '<br>本季度起始时间:<br>';
        echo date('Y-m-d H:i:s', mktime(0, 0, 0,$season*3-3+1,1,date('Y'))),"\n";
        echo date('Y-m-d H:i:s', mktime(23,59,59,$season*3,date('t',mktime(0, 0 , 0,$season*3,1,date("Y"))),date('Y'))),"\n";

        $season = ceil((date('n'))/3)-1;//上季度是第几季度
        echo '<br>上季度起始时间:<br>';
        echo date('Y-m-d H:i:s', mktime(0, 0, 0,$season*3-3+1,1,date('Y'))),"\n";
        echo date('Y-m-d H:i:s', mktime(23,59,59,$season*3,date('t',mktime(0, 0 , 0,$season*3,1,date("Y"))),date('Y'))),"\n";

        $season = ceil((date('n'))/3)-2;//当月是第几季度
        echo '<br>上一个季度起始时间:<br>';
        echo date('Y-m-d H:i:s', mktime(0, 0, 0,$season*3-3+1,1,date('Y'))),"\n";
        echo date('Y-m-d H:i:s', mktime(23,59,59,$season*3,date('t',mktime(0, 0 , 0,$season*3,1,date("Y"))),date('Y'))),"\n";

        $season = ceil((date('n'))/3)-3;//当月是第几季度
        echo '<br>上二个季度起始时间:<br>';
        echo date('Y-m-d H:i:s', mktime(0, 0, 0,$season*3-3+1,1,date('Y'))),"\n";
        echo date('Y-m-d H:i:s', mktime(23,59,59,$season*3,date('t',mktime(0, 0 , 0,$season*3,1,date("Y"))),date('Y'))),"\n";
    }

    public function test3($startTime='2016-01-02 00:00:00',  $endTime = "2017-05-30 23:59:59"){
        $l_start =  strtotime($startTime);
        $l_end = strtotime($endTime);
        echo $l_end. ":".$l_start;
        $sessions = [];
        $season = ceil((date('n'))/3);//当月是第几季度
        $sessions[]=array(mktime(0, 0, 0,$season*3-3+1,1,date('Y')),mktime(23,59,59,$season*3,date('t',mktime(0, 0 , 0,$season*3,1,date("Y"))),date('Y')));
        $season = ceil((date('n'))/3)-1;//上季度是第几季度
        $sessions[]=array(mktime(0, 0, 0,$season*3-3+1,1,date('Y')),mktime(23,59,59,$season*3,date('t',mktime(0, 0 , 0,$season*3,1,date("Y"))),date('Y')));
        $season = ceil((date('n'))/3)-2;//当月是第几季度
        $sessions[]=array(mktime(0, 0, 0,$season*3-3+1,1,date('Y')),mktime(23,59,59,$season*3,date('t',mktime(0, 0 , 0,$season*3,1,date("Y"))),date('Y')));
        $season = ceil((date('n'))/3)-3;//当月是第几季度
        $sessions[]=array(mktime(0, 0, 0,$season*3-3+1,1,date('Y')),mktime(23,59,59,$season*3,date('t',mktime(0, 0 , 0,$season*3,1,date("Y"))),date('Y')));
        var_dump($sessions);

        $tmp1 = [];
        foreach($sessions as $k => $v){
            $tmp1[] = array(date("Y-m-d",$v[0]),date("Y-m-d",$v[1]));
        }

        $result = [];
        $open = true;
        foreach($sessions as $k => $v){
            if($v[0] > $l_end){
                continue;
            } else {
                if($open){
                    $result[] = array($v[0], $l_end);
                    $open = false;
                    continue;
                }
            }
            if($v[0] >= $l_start){
                $result[] = $v;
            }

            if($l_start > $v[1]){
                break;
            } else {
                if($l_start >= $v[0] && $l_start <= $v[1]){
                    $result[] = array($l_start, $v[1]);
                }
            }
        }
        $tmp2 = [];
        foreach($result as $k => $v){
            $tmp2[] = array(date("Y-m-d",$v[0]),date("Y-m-d",$v[1]));
        }
        var_dump($tmp1);
        var_dump($tmp2);

//        var_dump($sessions);
//        var_dump($result);

    }




    public function export2Excel($expTitle, $expCellName, $expTableData)
    {
        if (is_array($expTableData)) {
            $year = date('Y');
            $month = date('m');
            $quater = intval($month) / 3 + 1;
            $mapping = array(
                1 => "一",
                2 => "二",
                3 => "三",
                4 => "四"
            );
            $title = $year . "年第" . $mapping[$quater] . "季度" . $month . "月份车间验收问题汇总反馈";
            $xlsTitle = iconv('utf-8', 'gb2312', $title);//文件名称
            $fileName = $title . date('YmdHis');//or $xlsTitle 文件名称可根据自己情况设定
            $cellNum = count($expCellName);


            vendor("PHPExcel.PHPExcel");

            $objPHPExcel = new \PHPExcel();
            $cellName = array('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z', 'AA', 'AB', 'AC', 'AD', 'AE', 'AF', 'AG', 'AH', 'AI', 'AJ', 'AK', 'AL', 'AM', 'AN', 'AO', 'AP', 'AQ', 'AR', 'AS', 'AT', 'AU', 'AV', 'AW', 'AX', 'AY', 'AZ');

            $teamNum = count($expTableData);


            for ($x=0; $x<=2; $x++) {
                $line = 1;
                $objPHPExcel->createSheet();
                $objPHPExcel->setActiveSheetIndex($x);
                $objPHPExcel->getActiveSheet()->setTitle($title);
                if($teamNum == 0){
                    $objPHPExcel->getActiveSheet()->mergeCells('A' . $line . ":" . $cellName[$cellNum - 1] . $line);//合并单元格
                    $objPHPExcel->getActiveSheet()->setCellValue('A' . $line, $title);
                    $objPHPExcel->getActiveSheet()->getStyle('A'.$line)->getFont()->setSize(16);
                    $objPHPExcel->getActiveSheet()->getStyle('A'.$line)->getFont()->setBold(true);
                    $objPHPExcel->getActiveSheet()->getStyle('A' . $line)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
                    for ($i = 0; $i < $cellNum; $i++) {
                        if ($i == $cellNum - 1) {
                            $objPHPExcel->getActiveSheet()->getColumnDimension($cellName[$i])->setWidth('60');
//                        $objPHPExcel->getActiveSheet($x)->mergeCells($cellName[$i] . strval($line + 1).":".$cellName[$i+6].strval($line + 1));//合并单元格
                        }
                        $objPHPExcel->getActiveSheet()->setCellValue($cellName[$i] . strval($line + 1), $expCellName[$i][1]);

                    }
                } else {
                    for ($team_idx = 0; $team_idx < $teamNum; $team_idx++) {
                        $depNum = count($expTableData[$team_idx]);
                        $objPHPExcel->getActiveSheet()->mergeCells('A' . $line . ":" . $cellName[$cellNum - 1] . $line);//合并单元格
                        $objPHPExcel->getActiveSheet()->setCellValue('A' . $line, $title);
                        $objPHPExcel->getActiveSheet()->getStyle('A'.$line)->getFont()->setSize(16);
                        $objPHPExcel->getActiveSheet()->getStyle('A'.$line)->getFont()->setBold(true);
                        $objPHPExcel->getActiveSheet()->getStyle('A' . $line)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
                        for ($i = 0; $i < $cellNum; $i++) {
                            if ($i == $cellNum - 1) {
                                $objPHPExcel->getActiveSheet()->getColumnDimension($cellName[$i])->setWidth('60');
                            }
                            $objPHPExcel->getActiveSheet()->setCellValue($cellName[$i] . strval($line + 1), $expCellName[$i][1]);
                        }

                        $newline = $line + 1 + $depNum;
                        $name = array();
                        for ($dep_idx = 0; $dep_idx < $depNum; $dep_idx++) {
                            $dep = $expTableData[$team_idx][$dep_idx];
                            for ($j = 0; $j < $cellNum; $j++) {
                                $name[$expCellName[$j][0]] = $dep[$expCellName[$j][0]];
                                $indic = $dep_idx + $line + 2;
                                if ($j == $cellNum - 1) {
                                    $objPHPExcel->getActiveSheet()->getColumnDimension($cellName[$j])->setWidth('60');
                                }
                                $objPHPExcel->getActiveSheet()->setCellValue($cellName[$j] . ($indic), $dep[$expCellName[$j][0]]);
                                $objPHPExcel->getActiveSheet()->getStyle($cellName[$j] . ($indic))->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_LEFT);//字体变小以适应宽
                                $objPHPExcel->getActiveSheet()->getStyle($cellName[$j] . ($indic))->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);//字体变小以适应宽
                                $objPHPExcel->getActiveSheet()->getStyle($cellName[$j] . ($indic))->getAlignment()->setWrapText(true);//自动换行
                                $name[$expCellName[$j][0]] = $dep[$expCellName[$j][0]];
                            }
                        }

                        //echo 'A'.strval($line + 2).':A' . $newline."<p>";
                        $indic2 = $line + 2;
                        $objPHPExcel->getActiveSheet()->mergeCells('A' . strval($indic2) . ':A' . $newline);
                        $objPHPExcel->getActiveSheet()->setCellValue('A' . strval($indic2), $name['sec_name']);
                        $objPHPExcel->getActiveSheet()->getStyle('A' . strval($indic2))->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
                        $objPHPExcel->getActiveSheet()->mergeCells('B' . strval($indic2) . ':B' . $newline);
                        $objPHPExcel->getActiveSheet()->setCellValue('B' . strval($indic2), $name['team_name']);
                        $objPHPExcel->getActiveSheet()->getStyle('B' . strval($indic2))->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
                        $line = $newline + 2;
                    }
                }
            }






            header('pragma:public');
            header('Content-type:application/vnd.ms-excel;charset=utf-8;name="' . $xlsTitle . '.xls"');
            header("Content-Disposition:attachment;filename=$fileName.xls");//attachment新窗口打印inline本窗口打印
            $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
            $objWriter->save('php://output');
            exit;
        }
    }


    public function exportExcel($expTitle, $expCellName, $expTableData)
    {
        $xlsTitle = iconv('utf-8', 'utf-8', $expTitle);//文件名称
        $fileName = $_SESSION['account'] . date('_YmdHis');//or $xlsTitle 文件名称可根据自己情况设定
        $cellNum = count($expCellName);
        $dataNum = count($expTableData);

        vendor("PHPExcel.PHPExcel");

        $objPHPExcel = new \PHPExcel();
        $cellName = array('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z', 'AA', 'AB', 'AC', 'AD', 'AE', 'AF', 'AG', 'AH', 'AI', 'AJ', 'AK', 'AL', 'AM', 'AN', 'AO', 'AP', 'AQ', 'AR', 'AS', 'AT', 'AU', 'AV', 'AW', 'AX', 'AY', 'AZ');

        $objPHPExcel->getActiveSheet(0)->mergeCells('A1:' . $cellName[$cellNum - 1] . '1');//合并单元格
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A1', $expTitle . '  Export time:' . date('Y-m-d H:i:s'));


//        $objPHPExcel->setActiveSheetIndex(0)->mergeCells('A2:A'.$dataNum+1);
//        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A2',$expTitle);

        for ($i = 0; $i < $cellNum; $i++) {
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue($cellName[$i] . '2', $expCellName[$i][1]);
        }
        $end = $dataNum + 3;
        $objPHPExcel->setActiveSheetIndex(0)->mergeCells('A3:A' . $end);
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A3', $expTitle);
        // Miscellaneous glyphs, UTF-8
        for ($i = 1; $i < $dataNum; $i++) {
            for ($j = 1; $j < $cellNum; $j++) {
                $objPHPExcel->getActiveSheet(0)->setCellValue($cellName[$j] . ($i + 3), $expTableData[$i][$expCellName[$j][0]]);
            }
        }

        header('pragma:public');
        header('Content-type:application/vnd.ms-excel;charset=utf-8;name="' . $xlsTitle . '.xls"');
        header("Content-Disposition:attachment;filename=$fileName.xls");//attachment新窗口打印inline本窗口打印
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        $objWriter->save('php://output');
        exit;
    }


    function expIssue()
    {
        $params = I("param.");
        if (empty($_GET['p'])) {
            $_GET['p'] = 0;
        }
        $xlsName = "Issue";
        $xlsCell = array(
            array('sec_name', "工务段"),
            array('team_name', '班组'),
            array('dep_name', '科室'),
            array('text', '验收发现问题')
        );
        $xlsModel = M('Issue');
        $wheres['i.time'] = array();
        $wheres['i.time'][] = array('gt', 0);

        if (!empty($params['begintime'])) {
            $begintime = strtotime($params['begintime']);
            $wheres['i.time'][] = array('egt', $begintime);
        }
        if (!empty($params['endtime'])) {
            $endtime = strtotime($params['endtime']);
            $wheres['i.time'][] = array('elt', $endtime);
        }

        if (intval($params['sec_id'], 0) > 0) {
            $wheres['i.sec_id'] = $params['sec_id'];
        }
        if (intval($params['dep_id'], 0) > 0) {
            $wheres['i.dep_id'] = $params['dep_id'];
        }
        if (intval($params['team_id'], 0) > 0) {
            $wheres['i.team_id'] = $params['team_id'];
        }
        if (!empty($params['issueregex'])) {
            $wheres['i.text'] = array('like', '%' . $params["issueregex"] . '%');
        }

        $wheress = array();
        $wheres[] = "1=1";
        if (!empty($wheres['i.sec_id'])) {
            $wheress['sec_id'] = $wheres['i.sec_id'];
        }

        if (!empty($wheres['i.team_id'])) {
            $wheress['team_id'] = $wheres['i.team_id'];
        }

        Log::write(print_r($wheres,true));
        $teamlist = $xlsModel->distinct(true)->where($wheress)->field("team_id")->select();
        Log::write(print_r($teamlist,true));
        $content = array();
        $team_ids = array();
        foreach ($teamlist as $k => $v) {
            $team_ids[] = $v['team_id'];
        }

        $map['team_id'] = array("in", $team_ids);
        $teams = M("Team")->where($map)->select();
        foreach ($teams as $k => $v) {
            $team_id = $v['team_id'];
            //"i.id", "i.sec_id", "i.dep_id", "i.time",
            $map = $wheres;
            $map['i.team_id|i.team_ids'] = array($team_id,array(array('like','%,'.$team_id.',%'), array('like',$team_id.',%'), array('like','%,'.$team_id),'or'),'_multi'=>true);
            $list = $xlsModel->alias("i")->field(array("w.sec_name", "t.team_name", "d.dep_name", "i.text"))->join(array(" JOIN __WORKSECTION__ w ON w.sec_id = i.sec_id", " JOIN __DEPARTMENT__ d ON d.dep_id = i.dep_id",
                " JOIN __TEAM__ t ON (t.team_id = i.team_id)"))->where($map)->order(array('i.team_id' => 'asc', 'i.dep_id' => 'asc', 'i.time' => 'desc'))->select();
            if(!empty($list)){
                $content[] = $list;
            }
        }
        Log::write(print_r($content,true));
//        var_dump($content);

        //$this->ajaxReturn(sp_ajax_return($content, "Success", 0));
//        $list = $xlsModel->alias("i")->field(array("i.id", "i.sec_id", "i.dep_id", "i.time", "i.text", "w.sec_name", "d.dep_name", "t.team_name"))->join(array("LEFT JOIN __WORKSECTION__ w ON w.sec_id = i.sec_id", "LEFT JOIN __DEPARTMENT__ d ON d.dep_id = i.dep_id",
//            "LEFT JOIN __TEAM__ t ON t.team_id = i.team_id"))->where($wheres)->order(array('i.team_id'=>'asc','i.dep_id'=>'asc','i.time' => 'desc'))->select();
//        $xlsData  = $xlsModel->field('id,truename,sex,res_id,sp_id,class,year,city,company,zhicheng,zhiwu,jibie,tel,qq,email,honor,remark')->select();
//        foreach ($xlsData as $k => $v)
//        {
//            $xlsData[$k]['sex']=$v['sex']==1?'男':'女';
//        }
        $this->export2Excel($xlsName, $xlsCell, $content);
    }


    /**
     *
     * 导出Excel
     */
    function expUser()
    {//导出Excel
        $xlsName = "User";
//        $xlsCell  = array(
//            array('id','账号序列'),
//            array('team_name','班组'),
//            array('dep_name','科室'),
//            array('text','验收发现问题'),
//            array('sp_id','专业')
//        );
        $xlsCell = array(
            array('id', '账号序列'),
            array('truename', '名字'),
            array('sex', '性别'),
            array('res_id', '院系'),
            array('sp_id', '专业'),
            array('class', '班级'),
            array('year', '毕业时间'),
            array('city', '所在地'),
            array('company', '单位'),
            array('zhicheng', '职称'),
            array('zhiwu', '职务'),
            array('jibie', '级别'),
            array('tel', '电话'),
            array('qq', 'qq'),
            array('email', '邮箱'),
            array('honor', '荣誉'),
            array('remark', '备注')
        );
        $xlsModel = M('Member');
        $xlsData = $xlsModel->field('id,truename,sex,res_id,sp_id,class,year,city,company,zhicheng,zhiwu,jibie,tel,qq,email,honor,remark')->select();
        foreach ($xlsData as $k => $v) {
            $xlsData[$k]['sex'] = $v['sex'] == 1 ? '男' : '女';
        }
        $this->exportExcel($xlsName, $xlsCell, $xlsData);

    }

}